# import pymssql
# # Server=localhost;Database=master;Trusted_Connection=True;
# # Replace the following with your actual database connection details
# server = '192.168.12.89'
# user = 'ass'
# password = '123'
# database = 'test'
#
# # Create a connection
# conn = pymssql.connect(server, user, password, database)
#
# # Create a cursor object
# cursor = conn.cursor()
#
# # Example: Execute a SQL query
# cursor.execute("SELECT * FROM test_table")
# rows = cursor.fetchall()
#
# # Process the query result
# for row in rows:
#     print(row)
#
# # Close the cursor and connection
# cursor.close()
# conn.close()





# import pyodbc
#
# server = 'ch71w0120'  # 或者是IP地址
# database = 'LineKitting'
# username = 'MdcsSzPr-rw'
# password = 'X7tu5#99RT1'
#
# # server = 'CH3UW3076'  # 或者是IP地址
# # database = 'MDCSPr'
# # username = 'MdcsSzPr-rw'
# # password = 'X7tu5#99RT1'
#
# cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
# cursor = cnxn.cursor()
#
# # 执行查询或命令
# cursor.execute("SELECT * FROM Kitting_User")
#
# # 获取结果
# rows = cursor.fetchall()
# for row in rows:
#     print(row)
#
# # 关闭游标和连接
# cursor.close()
# cnxn.close()



# python -m pip install SQLAlchemy -i https://pypi.tuna.tsinghua.edu.cn/simple
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

server = 'CH3UW3076'  # 或者是IP地址
database = 'MDCSPr'
username = 'MdcsSzPr-rw'
password = 'X7tu5#99RT1'

# server = 'ch71w0120'  # 或者是IP地址
# database = 'LineKitting'
# username = 'MdcsSzPr-rw'
# password = 'X7tu5#99RT1'

engine = create_engine(f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server')

Session = sessionmaker(bind=engine)
session = Session()

# 执行查询
result = session.execute(text("SELECT TOP 10 * FROM tblItems"))
# result = session.execute(text("SELECT * FROM Kitting_User"))
res = []
for row in result:
    # print(row)
    record_dict = dict(zip(result.keys(), row))
    # print(record_dict)
    res.append(record_dict)

# 关闭session
session.close()


## insert

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

# 初始化数据库
engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)

DBSession = sessionmaker(bind=engine)
session = DBSession()

# 插入数据
new_user = User(name='John Doe', email='john.doe@example.com')
session.add(new_user)

try:
    session.commit()
except Exception as e:
    session.rollback()
    print(f"Error occurred: {e}")
else:
    print("User inserted successfully.")